<?php
class SelectAppByLCROE extends CommonDAO {
	
	public function SelectAppByLCROE($pdbConnection){
		$this->setDBConnection($pdbConnection);
	}
	
	private $sqlSelect = '
	SELECT
		R.PING_ID 	AS PING_NUMBER, 
		R.NAME1		AS FIRST_NAME, 
		R.NAME2 	AS SECOND_NAME, 
		R.LASTNAME1 	AS FIRST_LAST_NAME, 
		R.LASTNAME2	AS SECOND_LAST_NAME, 
		R.EMAIL 	AS EMAIL,
		RT.NAME_P 	AS TYPE_ID,
		R.ID 		AS IDENTIFICATION,
		R.BIRTHDAY 	AS BIRTH_DAY,
		R.FROMCITY 	AS FROM_CITY,
		R.ADDRESPLACE 	AS ADDRES_PLACE,
		R.PHONE 	AS PHONE,
		R.CELPHONE 	AS CELPHONE,
		R.CREATION_DATE AS CREATION_DATE,
		IFNULL(RU.NAME_P,R.OTHER_U)	AS UNIVERSITY,
		R.OTHER_U				AS OTHER_U,
		IFNULL(RC.NAME_P,R.OTHER_U_PROG) AS CARRER,
		R.OTHER_U_PROG	AS OTHER_CARRER,
		R.SEMESTER 	AS SEMESTER,
		R.ACADEMIC_HOUR AS ACADEMIC_HOUR,
		R.AIESEC_HOUR 	AS AIESEC_HOUR,
		RW1.NAME_COMPANY AS WORK_NAME_COMPANY1,
		RW1.DURATION_MONTH AS WORK_DURATION1,
		RW1.RESPONSABILITIES AS WORK_RESPONSABILITITES2,
		RW2.NAME_COMPANY AS WORK_NAME_COMPANY2,
		RW2.DURATION_MONTH AS WORK_DURATION2,
		RW2.RESPONSABILITIES AS WORK_RESPONSABILITITES2,
		RW3.NAME_COMPANY AS WORK_NAME_COMPANY3,
		RW3.DURATION_MONTH AS WORK_DURATION3,
		RW3.RESPONSABILITIES AS WORK_RESPONSABILITITES3,
		RL1TP.NAME_P 	AS TP_LANGUAGE1,
		RL1RLV.NAME_P 	AS LANGUAGE_READING1,
		RL1WLV.NAME_P 	AS LANGUAGE_WRITING1,
		RL1CLV.NAME_P 	AS LANGUAGE_COMP1,
		RL2TP.NAME_P 	AS TP_LANGUAGE2,
		RL2RLV.NAME_P 	AS LANGUAGE_READING2,
		RL2WLV.NAME_P 	AS LANGUAGE_WRITING2,
		RL2CLV.NAME_P 	AS LANGUAGE_COMP2,
		RL3TP.NAME_P 	AS TP_LANGUAGE3,
		RL3RLV.NAME_P 	AS LANGUAGE_READING3,
		RL3WLV.NAME_P 	AS LANGUAGE_WRITING3,
		RL3CLV.NAME_P 	AS LANGUAGE_COMP3,
		"VER.2"		AS SKILLS_TO_DEVELOP,
		"VER.2"		AS STRONG_COMPETENCES,
		"VER.2"		AS WEAK_COMPETENCES,
		R.INTERES_LIVE_X_EXP 	AS INTERES_LIVE_X_EXP,
		R.S_TO_LIVE_X_EXP 	AS SEMESTER_TO_LIVE_X_EXP,
		R.DURATION_OF_X_EXP 	AS DURATION_OF_X_EXP,
		R.MEDIA_TO_KNOW_AIESEC	AS MEDIA_TO_KNOW_AIESEC,
		R.WHY_DECIDE_APPLY	AS WHY_DECIDE_APPLY,
		R.HOW_YOU_CONTRIBUTION	AS HOW_YOU_CONTRIBUTION
		FROM REQ_FORM_BASE R 
		LEFT OUTER JOIN REQ_TP_ID RT ON R.ID_REQ_TP_ID=RT.ID_REQ_TP_ID 
		LEFT OUTER JOIN REQ_UNIVERSITY RU ON R.ID_REQ_UNIVERSITY=RU.ID_REQ_UNIVERSITY
		LEFT OUTER JOIN REQ_COMMON_U_PROG RC ON R.ID_REQ_COMMON_U_PROG=RC.ID_REQ_COMMON_U_PROG
		LEFT OUTER JOIN REQ_FORM_WORK_EXP RW1 ON RW1.ID_REQ_FORM_BASE=R.ID_REQ_FORM_BASE AND RW1.NUMBER = 1
		LEFT OUTER JOIN REQ_FORM_WORK_EXP RW2 ON RW2.ID_REQ_FORM_BASE=R.ID_REQ_FORM_BASE AND RW2.NUMBER = 2
		LEFT OUTER JOIN REQ_FORM_WORK_EXP RW3 ON RW3.ID_REQ_FORM_BASE=R.ID_REQ_FORM_BASE AND RW3.NUMBER = 3
		LEFT OUTER JOIN REQ_FORM_LANGUAJE_EXP RL1 ON RL1.ID_REQ_FORM_BASE=R.ID_REQ_FORM_BASE AND RL1.NUMBER = 1
		LEFT OUTER JOIN REQ_TP_LANGUAJE RL1TP ON RL1TP.ID_REQ_TP_LANGUAJE=RL1.ID_REQ_TP_LANGUAJE 
		LEFT OUTER JOIN REQ_TP_LEVEL RL1RLV ON RL1RLV.ID_REQ_TP_LEVEL = RL1.ID_REQ_TP_LEVEL_READ
		LEFT OUTER JOIN REQ_TP_LEVEL RL1WLV ON RL1WLV.ID_REQ_TP_LEVEL = RL1.ID_REQ_TP_LEVEL_WRITE
		LEFT OUTER JOIN REQ_TP_LEVEL RL1CLV ON RL1CLV.ID_REQ_TP_LEVEL = RL1.ID_REQ_TP_LEVEL_COMP
		LEFT OUTER JOIN REQ_FORM_LANGUAJE_EXP RL2 ON RL2.ID_REQ_FORM_BASE=R.ID_REQ_FORM_BASE AND RL2.NUMBER = 2
		LEFT OUTER JOIN REQ_TP_LANGUAJE RL2TP ON RL2TP.ID_REQ_TP_LANGUAJE=RL2.ID_REQ_TP_LANGUAJE 
		LEFT OUTER JOIN REQ_TP_LEVEL RL2RLV ON RL2RLV.ID_REQ_TP_LEVEL = RL2.ID_REQ_TP_LEVEL_READ
		LEFT OUTER JOIN REQ_TP_LEVEL RL2WLV ON RL2WLV.ID_REQ_TP_LEVEL = RL2.ID_REQ_TP_LEVEL_WRITE
		LEFT OUTER JOIN REQ_TP_LEVEL RL2CLV ON RL2CLV.ID_REQ_TP_LEVEL = RL2.ID_REQ_TP_LEVEL_COMP
		LEFT OUTER JOIN REQ_FORM_LANGUAJE_EXP RL3 ON RL3.ID_REQ_FORM_BASE=R.ID_REQ_FORM_BASE AND RL3.NUMBER = 3
		LEFT OUTER JOIN REQ_TP_LANGUAJE RL3TP ON RL3TP.ID_REQ_TP_LANGUAJE=RL3.ID_REQ_TP_LANGUAJE 
		LEFT OUTER JOIN REQ_TP_LEVEL RL3RLV ON RL3RLV.ID_REQ_TP_LEVEL = RL3.ID_REQ_TP_LEVEL_READ
		LEFT OUTER JOIN REQ_TP_LEVEL RL3WLV ON RL3WLV.ID_REQ_TP_LEVEL = RL3.ID_REQ_TP_LEVEL_WRITE
		LEFT OUTER JOIN REQ_TP_LEVEL RL3CLV ON RL3CLV.ID_REQ_TP_LEVEL = RL3.ID_REQ_TP_LEVEL_COMP
		WHERE R.ID_REQ_LC = ';
	
	/**
	 * return the informacion in statement base on mysql_query php function
	 * @param $lc
	 * @return resource For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query returns a resource on success, or false on error.
	 */
	public function getStmtByLC($lc){
		$this->sqlSelect = $this->sqlSelect . $lc ;
		$db = $this->getDBConnection();
		$stmt=$db->exec($this->sqlSelect);
		return $stmt;
	}
}